#!/usr/bin/env python
# encoding: utf - 8
"""
#-------------------------------------------------------------------#
#                   CONFIDENTIAL --- CUSTOM STUDIOS                 #     
#-------------------------------------------------------------------#
#                                                                   #
#                   @Project Name : Globallawonline                #
#                                                                   #
#                   @File Name    : jgfytoxml.py                      #
#                                                                   #
#                   @Programmer   : 李建                            #
#                                                                   #  
#                   @Start Date   : 2021/4/8 17:22                 #
#                                                                   #
#                   @Last Update  : 2021/4/8 17:22                 #
#                                                                   #
#-------------------------------------------------------------------#
# Classes:该类用于将加工服务器上待翻译数据导出为xml文件翻译后再导入                                                          #
#                                                                   #
#-------------------------------------------------------------------#
"""
import re
from shutil import copyfile
from xml.dom import minidom
import os

import pymysql

from Seetings import JGMYSQL_CONNEXT


class FYTOXML:
    def __init__(self):
        self.jg_conn = pymysql.connect(host=JGMYSQL_CONNEXT["MYSQL_HOST"],
                                  port=JGMYSQL_CONNEXT["MYSQL_PORT"],
                                  db=JGMYSQL_CONNEXT["MYSQL_DB"],
                                  user=JGMYSQL_CONNEXT["MYSQL_USER"],
                                  passwd=JGMYSQL_CONNEXT["MYSQL_PASSWD"],
                                  charset=JGMYSQL_CONNEXT["MYSQL_CHARSET"])

    def __del__(self):
        self.jg_conn.close()

    def __reConnect(self):
        """
        查看数据库连接是否有效
        :return:
        """
        try:
            self.jg_conn.ping()
        except:
            self.jg_conn()

    def __getsql(self, table, field_list: list, language, full_field: bool):
        """
        待翻译数据导出为xml文件的sql数据提取函数
        :param table: 导出表
        :param field_list: 导出字段
        :param language: 导出语种
        :return:
        """
        out_path = input("请输入输出目录:")
        try:
            file_out_path = os.path.join(out_path)
        except:
            file_out_path = os.getcwdb()
            print("input erro:将使用当前工作目录")
        self.__reConnect()  # 判断数据库连接是否断开
        field_str = ','.join(field_list)
        field_empty_str = ""
        if not full_field:
            for field in field_list:
                if language == "LANGUAGEYY":
                    file_str = '''(%s IS NOT NULL AND %s != "" ) AND (%s IS NULL OR %s = "" OR %s IS NULL OR %s = "")''' % (
                    field, field, str(field + "C"), str(field + "C"), str(field + "E"), str(field + "E"))
                    if len(field_empty_str):
                        field_empty_str = field_empty_str + '''OR (%s)''' % file_str
                    else:
                        field_empty_str = file_str
                else:
                    file_str = '''(%s IS NOT NULL AND %s != "" ) AND (%s IS NULL OR %s = "" OR %s IS NULL OR %s = "" OR %s IS NULL OR %s = "")''' % (
                        field, field, str(field + "C"), str(field + "C"), str(field + "E"), str(field + "E"),
                        str(field + "X"), str(field + "X"))
                    if len(field_empty_str):
                        field_empty_str = field_empty_str + '''OR (%s)''' % file_str
                    else:
                        field_empty_str = file_str
            # sql = '''SELECT SYSID,%s from %s where SortB = "%s" AND (%s) AND SYS_FLD_IMPORTKBASE = "1"''' % (
            #     field_str, table, language, field_empty_str)
            # sql = '''SELECT SYSID,Title from `case` where SortA = "LAWCOUNTRYXJP"'''
            sql = '''SELECT SYSID,%s from %s where SYS_FLD_MARKSTATE = "999" AND SortB = "%s" AND (%s)''' % (
                field_str, table, language, field_empty_str)
        else:
            sql = '''SELECT SYSID,%s from %s where SYS_FLD_MARKSTATE = "999" AND SortB = "%s"''' % (
                field_str, table, language)
        un_translates = []
        try:
            with self.jg_conn.cursor() as jg_cursor:
                jg_cursor.execute(sql)
                un_translates = jg_cursor.fetchall()
        except Exception as e:
            print("加工服务器查询出错:%s(%s)" % (str(e), sql))
        valuedict = {}
        try:
            for un_translate in un_translates:
                out_path_flie = os.path.join(file_out_path, un_translate[0] + ".xml")
                i = 1
                for field in field_list:
                    if un_translate[i] == None:
                        valuedict[field] = ""
                    else:
                        valuedict[field] = un_translate[i]
                    i = i + 1
                self.__makexml(out_path_flie, valuedict)
        except Exception as e:
            print("输出xml出错:%s" % str(e))

    def __makexml(self, out_path, dictvalue: dict):
        """
        制作xml文件
        :param out_path: xml文件保存全路径
        :param dictvalue: xml文件存放内容的字典
        :return:
        """
        # 创建Document
        xml = minidom.Document()

        # 创建root节点
        root = xml.createElement('book')
        xml.appendChild(root)

        # 创建二级节点
        for key in dictvalue.keys():
            # 创建一个带有文本的字节点
            value = xml.createElement(key)
            value.appendChild(xml.createTextNode(dictvalue[key]))
            # 子节点加入根节点
            root.appendChild(value)

        # 保存
        fp = open(out_path, 'w', encoding="utf-8")
        xml.writexml(fp, indent='  ', addindent='  ', newl='\n')
        fp.close()

    def __readXML(self, table, input_path, lang_suf, lang_sym, update_status):
        """
        解析xml文件
        :param table:数据库表名
        :param input_path:文件路径
        :param lang_suf: 入库语种后缀
        :param lang_sym: 翻译结果标志
        :param update_status: 是否需要更新数据状态
        :return: 一个值的字典
        """
        node_list_dict = {"`law`": ["LegalName", "Organizaation"],
                          "`case`": ["Title", "CaseOfAction", "UseLaw", "JudgAgency", "CaseSummary"],
                          "`treaty`": ["Title", "StateParty"]}
        node_list = node_list_dict[table]
        file = os.path.split(input_path)[1]
        # 解析xml文件
        try:
            domtree = minidom.parse(input_path)
            # 获取根节点
            datalist = domtree.documentElement
        except Exception as e:
            print("解析%sxml文件出错:%s" % (input_path, str(e)))
            return False
        # 获取标签子节点的值并存为字典
        resultdict = {}
        for node in node_list:
            if datalist.getElementsByTagName(node):
                if datalist.getElementsByTagName(node)[0].childNodes:
                    resultdict[node] = datalist.getElementsByTagName(node)[0].childNodes[0].data
        result = self.__writesql(table, file, resultdict, lang_suf, lang_sym, update_status)
        return result

    def __writesql(self, table, file_name, valuedict: dict, lang_suf, lang_sym, update_status):
        """
        将读取的xml数据入库
        :param table: 数据库待更新表名
        :param file_name:  xml文件名
        :param valuedict: 入库内容
        :param lang_suf: 入库语种后缀
        :param lang_sym: 翻译结果标志
        :param update_status: 是否需要更新数据状态
        :return:
        """
        self.__reConnect()  # 判断数据库连接是否断开
        restr = "%s.xml" % lang_sym
        sysid = re.sub('''(%s)$''' % restr, '''''', file_name)
        value_list = []  # 更新数据列表
        # 获取入库字段和入库字段值
        for key in valuedict.keys():
            values = pymysql.escape_string(valuedict[key])
            value_list.append('''%s%s="%s"''' % (key, lang_suf, values))
        value_list_str = ','.join(value_list)
        if update_status == 1:
            value_list_str = value_list_str + ''',SYS_FLD_MARKSTATE = "2"'''
        sql = '''update %s set %s where sysid = "%s"''' % (table, value_list_str, sysid)
        print(sql)
        try:
            with self.jg_conn.cursor() as jg_cursor:
                jg_cursor.execute(sql)
                self.jg_conn.commit()
        except Exception as e:
            print("加工服务器更新数据出错：%s(%s)" % (str(e), sql))
            return False
        return True

    def __xmlexport(self):
        """
        待翻译数据导出为xml文件
        :return:
        """
        # 输入xml带待翻译文件导出表
        table_num = input("选择导出表(法律:0; 判例:1; 国际条约:2)")
        table_dict = {"0": "`law`", "1": "`case`", "2": "`treaty`"}
        try:
            table = table_dict[table_num]
        except Exception as e:
            print("输入表超出范围:%s" % str(e))
            return None
        # 导出语种
        language_num = input("请选择导出语种(英语:1;泰文:2;印尼语:3;柬埔寨语:4;缅甸语:5;老挝语:6;马来语:7;越南语:8;中文:9)")
        language_dict = {"1": "LANGUAGEYY", "2": "LANGUAGETY", "3": "LANGUAGEYNIY", "4": "LANGUAGEGMY",
                         "5": "LANGUAGEMDY", "6": "LANGUAGELWY", "7": "LANGUAGEMLY", "8": "LANGUAGEYNY", "9": "LANGUAGEZW"}
        try:
            language = language_dict[language_num]
        except Exception as e:
            print("输入语种超出范围:%s" % str(e))
            return None
        # 是否全字段导出
        full_field_num = input("是否全字段导出(是:1; 否:0)")
        if full_field_num == "1":
            full_field = True
            field_list_dict1 = {"`law`": ["LegalName", "Organizaation"],
                                "`case`": ["Title", "CaseOfAction", "UseLaw", "JudgAgency", "CaseSummary"],
                                "`treaty`": ["Title", "StateParty"]}
            field_list = field_list_dict1[table]
        elif full_field_num == "0":
            full_field = False
            # 导出字段
            field_list_input_dict = {"`law`": "请选择导出字段(一次仅能检测一个字段):LegalName:1;Organizaation:2;",
                                     "`case`": "请选择导出字段(一次仅能检测一个字段):Title:1;CaseOfAction:2;UseLaw:5;JudgAgency:6;CaseSummary:7",
                                     "`treaty`": "请选择导出字段(一次仅能检测一个字段):Title:1;;StateParty:2"}
            field_list_num = input(field_list_input_dict[table])
            field_list_dict2 = {"`law`": {"1": "LegalName", "2": "Organizaation"},
                                "`case`": {"1": "Title", "2": "CaseOfAction", "5": "UseLaw", "6": "JudgAgency",
                                           "7": "CaseSummary"},
                                "`treaty`": {"1": "Title", "2": "StateParty"}}
            try:
                field_list = [field_list_dict2[table][field_list_num]]
            except Exception as e:
                print("输入字段超出范围:%s" % str(e))
                return None
        else:
            print("导出方式超出范围")
            return None
        self.__getsql(table, field_list, language, full_field)

    def __xmlimport(self):
        """
        xml翻译完毕文件导入
        :return:
        """
        # 输入翻译完毕xml文件路径
        file_root = input("please input import file path: ")
        try:
            file_root_path = os.path.join(file_root)
        except:
            print("input erro")
            return None
        # 输入入库表
        table_num = input("选择入库表(法律:0; 判例:1; 国际条约:2)")
        table_dict = {"0": "`law`", "1": "`case`", "2": "`treaty`"}
        try:
            table = table_dict[table_num]
        except Exception as e:
            print("输入表超出范围:%s" % str(e))
            return None
        # 输入入库语种
        lang_num = input("选择入库语言(中文:0; 英文:1; 小语种:2):")
        # 入库语种后缀
        lang_suf_dict = {"0": "C", "1": "E", "2": "X"}
        try:
            lang_suf = lang_suf_dict[lang_num]
        except Exception as e:
            print("输入语言超出范围:%s" % str(e))
            return None
        # 输翻译结果后缀
        lang_sym_num = input("选择翻译结果标志(无:0; e:1; c:2):")
        lang_sym_dict = {"0": "", "1": "e", "2": "c"}
        try:
            lang_sym = lang_sym_dict[lang_sym_num]
        except Exception as e:
            print("输入标志超出范围:%s" % str(e))
            return None
        # 是否更新数据状态
        update_status_num = input("是否更新数据状态(不更新:0; 更新:1)")
        try:
            update_status = int(update_status_num)
        except:
            update_status = 0
        # 将每篇翻译完成的xml导入sql
        for file in os.listdir(file_root_path):
            file_path = os.path.join(file_root_path, file)
            # xml文件导入sql
            result = self.__readXML(table, file_path, lang_suf, lang_sym, update_status)
            if not result:
                # 导入错误数据存入错误文件夹
                if not os.path.exists(file_root_path + '\\erro'):
                    os.mkdir(file_root_path + '\\erro')
                copyfile(file_path, file_root_path + '\\erro\\' + file)

    def Interface(self):
        # 选择操作类型
        operate_num = input("请选择操作类型(待翻译数据导出为xml文件:0;xml翻译完毕文件导入:1)")
        if operate_num == "0":
            self.__xmlexport()
        elif operate_num == "1":
            self.__xmlimport()
        else:
            print("操作类型输入出错")



if __name__ == '__main__':
    FYTOXML().Interface()

